package com.iris.live.services.data.repositories;

import com.iris.live.services.data.models.Lsh车型Model;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * Created by sxy on 2016/7/12.
 */
@Component("carRepository")
@Transactional
public interface Lsh车型Repository extends JpaRepository<Lsh车型Model, Integer>, JpaSpecificationExecutor<Lsh车型Model> {
    @Modifying
    @Query("update Lsh车型Model a set a.座位数=?2 where a.车型编号=?1")
    int updateSeatsNumberById(int carTypeId,String SeatsNumber);

    @Query("select a from Lsh车型Model a where a.来源=?1 and a.品牌=?2 and a.级别=?3 order by a.车型编号")
    List<Lsh车型Model> findBy来源And品牌And级别(String source,String brand,String lv);

    @Query("select a from Lsh车型Model a where a.来源=?1 and a.品牌=?2 and a.级别 <>'' order by a.车型编号")
    List<Lsh车型Model> findBy来源And品牌(String source,String brand);

    @Query("select a from Lsh车型Model a where a.车型编号=?1")
    Lsh车型Model findby车型编号(int 车型编号);

    @Query("select a from Lsh车型Model a where a.来源=?1 and a.品牌=?2 and a.级别 =?3  " +
            "  and a.车型=?4 and     a.车型详细=?5   " +
            "  and a.type =?6   and a.variant=?7  ")
    List<Lsh车型Model> findBySevenElement(String origin, String brand, String carClass,
                                        String carModel, String carStyle, String Type, String Variant);
    @Query("select a.车型编号 from Lsh车型Model a where  a.品牌=?1 and a.级别=?2 and a.车型=?3 and a.车型详细=?4  ")
    Integer findByCarTypeInfo(String brand,String lv,String carType,String carInfo);

    @Query("select a from Lsh车型Model a where a.品牌=?1 and a.级别=?2 and a.车型=?3 and a.车型详细=?4")
    Lsh车型Model findCarModelInfo(String brand, String level, String model, String style);

    @Query("select a.车型编号 from Lsh车型Model a where  a.车型详细=?1  ")
    Integer findByCarDetail(String carDetail);

    @Query("select a from Lsh车型Model a where a.cPkid=?1  ")
    List<Lsh车型Model> findByCpkid(int source);

    @Modifying
    @Query("delete from Lsh车型Model a where a.来源=?1 and a.品牌=?2 and a.级别=?3  and a.车型=?4 and a.车型详细=''  ")
    void deleteSeatsNumberById(String carTypeId, String SeatsNumber, String carTypeIds, String SeatsNumbers);


    @Modifying
    @Query("delete from Lsh车型Model a where a.来源=?1 and a.品牌=?2 and a.级别=?3  and a.车型='' and a.车型详细=''  ")
    void deleteSeatsNumberByIds(String carTypeId, String SeatsNumber, String carTypeIds);

    @Modifying
    @Query("delete from Lsh车型Model a where a.来源=?1 and a.品牌=?2 and a.级别=''  and a.车型='' and a.车型详细=''  ")
    void deleteSeatsNumberByIdss(String carTypeId, String SeatsNumber);

    @Query("select a.车型编号 from Lsh车型Model a where a.车型详细 = ?1 order by a.车型编号")
    List<Integer> queryCarCodeByCarDetail(String carDetail);

    @Query(value = "select distinct 品牌  from Lsh车型 a where 品牌 is not null and a.品牌 <> ''" ,nativeQuery = true)
    List<String> selectAllBrands();


    @Query(value = "select a.品牌  from Lsh车型 a where a.品牌 is not null and a.品牌 <> '' group by a.品牌" ,nativeQuery = true)
    List<String> selectByModel();

    /**
     * 获得品牌和车系下的所有车型
     * @param brand
     * @param series
     * @return
     */
    @Query(value = "select 车型 from Lsh车型 where 品牌 = ?1 and 级别 = ?2 and 车型 is not null and 车型 <> '' group by 车型 ",nativeQuery = true)
    List<String> getCarModelByBrandAndSeries(String brand,String series);

    /**
     * 获得品牌下的所有车型
     * @param brand
     * @return
     */
    @Query(value = "select 车型 from lsh车型 where 品牌 = ?1 and 车型 is not null and 车型 <> '' group by 车型 ",nativeQuery = true)
    List<String> getCarModelByBrand(String brand);

    @Query(value = "select a.appearance from Lsh车型Model a where a.品牌 = ?1 and a.级别 = ?2 and a.车型 = ?3")
    List<String> getColorByCar(String selBrandname,String selClass,String selType);

    List<Lsh车型Model> findBy品牌And级别And车型(String selBrandname,String selClass,String selType);

    @Query(value = "select case when a.车型详细 = '' or a.车型详细 is null then '其他' else a.车型详细 end from Lsh车型Model a where a.品牌 = ?1 and a.级别 = ?2 and a.车型 = ?3")
    List<String> getCarDetailedByCar(String selBrandname,String selClass,String selType);

    @Query(value = "select b.* from group_area a INNER JOIN lsh车型 b ON LOCATE(b.品牌,a.brand) > 0  where permit = ?1 and b.车型详细 <>'' ORDER BY b.品牌, b.级别,b.车型,b.车型详细",nativeQuery = true)
    List<Lsh车型Model> getCarTreeByPermit(String permit);

}
